package Model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import Information.UserInfo;

public class UserInfomrmationDAO{
	
	private int UNO;
	private String UUser;
	private String UPassword;
	private String UName;
	private String UEmail;
	
	public UserInfomrmationDAO()
	{
		this.UNO=0;
		this.UUser = "";
		this.UPassword = "";
		this.UName = "";
		this.UEmail = "";
	}

	public boolean AddNewUser(String UUser,String UPassword,String UName,String UEmail) throws SQLException, ClassNotFoundException
	{
		String sql ="insert into Person (UUser,UPassword,UName,UEmail) values('"+UUser+"','"+UPassword+"','"+UName+"','"+UEmail+"')";
		Connection con = null;
		Statement stmt = null;
		DatabaseConfig dConfig = new DatabaseConfig();
		con = dConfig.getConnection();
		stmt = con.createStatement();
		if(stmt.executeUpdate(sql)==1)
		{
			return true;
		}
		else
		{
			return false;
		}
	}
	
	public ArrayList<UserInfo> SeeUser(String username) throws SQLException, ClassNotFoundException
	{
		ArrayList<UserInfo> arrayList = null;
		UserInfo userInfo = new UserInfo();
		String sql ="select * from Person where UUser ='"+username+"'";
		Connection con = null;
		Statement stmt = null;
		ResultSet result = null;
		DatabaseConfig dConfig = new DatabaseConfig();
		con = dConfig.getConnection();
		stmt = con.createStatement();
		result = stmt.executeQuery(sql);
		UNO = result.getInt("UNO");
		UUser = result.getString("UUser");
		UPassword = result.getString("UPassword");
		UName = result.getString("UName");
		UEmail = result.getString("UEmail");
		userInfo.SetUserInfo(UNO,UUser, UPassword, UName, UEmail);
		arrayList.add(userInfo);
		return arrayList;
	}
	
	public ArrayList<UserInfo> SeeAllUser() throws SQLException, ClassNotFoundException
	{
		ArrayList<UserInfo> arrayList = new ArrayList<UserInfo>();
		String sql ="select * from Person";
		Connection con = null;
		Statement stmt = null;
		ResultSet result = null;
		DatabaseConfig dConfig = new DatabaseConfig();
		con = dConfig.getConnection();
		stmt = con.createStatement();
		result = stmt.executeQuery(sql);
		while(result.next())
		{
			UserInfo userInfo = new UserInfo();
			UNO = result.getInt("UNO");
			UUser = result.getString("UUser");
			UPassword = result.getString("UPassword");
			UName = result.getString("UName");
			UEmail = result.getString("UEmail");
			userInfo.SetUserInfo(UNO,UUser, UPassword, UName, UEmail);
			arrayList.add(userInfo);
		}
		return arrayList;
	}
	
	public UserInfo FindUser(String username) throws SQLException, ClassNotFoundException
	{
		UserInfo userInfo = new UserInfo();
		String sql ="select * from Person where UUser ='"+username+"'";
		Connection con = null;
		Statement stmt = null;
		ResultSet result = null;
		DatabaseConfig dConfig = new DatabaseConfig();
		con = dConfig.getConnection();
		stmt = con.createStatement();
		result = stmt.executeQuery(sql);
		if(result.next())
		{
			UNO= result.getInt("UNO");
			UUser = result.getString("UUser");
			UPassword = result.getString("UPassword");
			UName = result.getString("UName");
			UEmail = result.getString("UEmail");
		}
		userInfo.SetUserInfo(UNO,UUser, UPassword, UName, UEmail);
		return userInfo;
	}
	
	public boolean DeleteUser(String username) throws ClassNotFoundException, SQLException
	{
		String sql ="delete from Person where UUser='"+username+"'";
		Connection con = null;
		Statement stmt = null;
		DatabaseConfig dConfig = new DatabaseConfig();
		con = dConfig.getConnection();
		stmt = con.createStatement();
		if(stmt.executeUpdate(sql)==1)
		{
			return true;
		}
		else
		{
			return false;
		}
	}
	
	public boolean UpdateUser(String UUser,String UPassword,String UName,String UEmail) throws ClassNotFoundException, SQLException
	{
		String sql ="update Person set UPassword='"+UPassword+"', UName='"+UName+"', UEmail='"+UEmail+"' where UUser='"+UUser+"'";
		Connection con = null;
		Statement stmt = null;
		DatabaseConfig dConfig = new DatabaseConfig();
		con = dConfig.getConnection();
		stmt = con.createStatement();
//		System.out.println(sql);
		if(stmt.executeUpdate(sql)==1)
		{
			return true;//update successfully
		}
		else
		{
			return false;//update failed
		}
	}
}